﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class PayablesObject
    {
        public const string sql = @"
SELECT DictionaryPartnersWork1.Name as Партнер,
DictionaryProjectWork1.Name as Проект,
COALESCE(Work1.CountWorks,0) as Кол_во_работ,
COALESCE(Work1.SumDolg,0) as Долг_перед_поставщиком,
COALESCE(DictionaryCurrencyWork.ShortName,'---') as Вал_долга,
--DictionaryPartnersTransactions1.Name as Партнер_фин,
--DictionaryProjectTransactions1.Name as Проект_фин,
COALESCE(Transactions1.SumOplat,0) as Оплачено_поставщику,
COALESCE(DictionaryCurrencyTransactions.ShortName,'---') as Вал_опл,
COALESCE(Transactions1.SumOplat,0) - COALESCE(Work1.SumDolg,0) as Сальдо
FROM		
              (SELECT [Work].[Supplier]
			   ,[Work].[Project]
			   ,COALESCE(Sum([Work].[CountWork]),0) as CountWorks
			   ,COALESCE(Sum([Work].[PriceOfPurchasing]),0) as SumDolg
			   ,[Work].[CurrencyPurchasing] as CurrencyPurchasing
		FROM   [Work]
              WHERE [Work].[Supplier] is not null
		Group by [Work].[Supplier], [Work].[Project], [Work].[CurrencyPurchasing]) as Work1
LEFT JOIN
		(SELECT [Transactions].[Partner]
			   ,[Transactions].[Project]
			   ,COALESCE(sum([Summa]),0) as SumOplat
			   ,[Transactions].[Currency] as Currency
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 2 and [Transactions].[Category] in (select Value from Settings where Code Like 'code_cat_racx_suppl%')
		Group by [Transactions].[Partner], [Transactions].[Project], [Transactions].[Currency]) as Transactions1
ON Work1.Supplier = Transactions1.Partner and Work1.Project = Transactions1.Project and Work1.CurrencyPurchasing = Transactions1.Currency
INNER JOIN DictionaryPartners as DictionaryPartnersWork1 on DictionaryPartnersWork1.ID = Work1.Supplier
--LEFT JOIN DictionaryPartners as DictionaryPartnersTransactions1 on DictionaryPartnersTransactions1.ID = Transactions1.Partner
INNER JOIN DictionaryProject as DictionaryProjectWork1 on DictionaryProjectWork1.ID = Work1.Project
--LEFT JOIN DictionaryProject as DictionaryProjectTransactions1 on DictionaryProjectTransactions1.ID = Transactions1.Project
INNER JOIN DictionaryCurrency as DictionaryCurrencyWork on DictionaryCurrencyWork.ID = Work1.CurrencyPurchasing
LEFT JOIN DictionaryCurrency as DictionaryCurrencyTransactions on DictionaryCurrencyTransactions.ID = Transactions1.Currency
";
        public string Партнер { get; set; }
        public string Проект { get; set; }
        public int? Кол_во_работ { get; set; }
        public decimal? Долг_перед_поставщиком { get; set; }
        public string Вал_долга { get; set; }
        public decimal? Оплачено_поставщику { get; set; }
        public string Вал_опл { get; set; }
        public decimal? Сальдо { get; set; }
    }
}
